<?php

namespace App\Console\Commands;

use App\Http\Services\Oss;
use Carbon\Carbon;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;
use PHPExcel;
use PHPExcel_IOFactory;
use App\Model\Statistics;

class CreateDayilyExcel extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'CreateDayilyExcel';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '生成每日数据Excel';

    protected $successLog = 'dailyExcel'; //日志
    protected $errorLog = 'error_dailyExcel'; //错误日志
    protected $apiCon = 'api'; //数据库
    protected $activityConn = 'activity';

    protected $fc_event_table = 'familycompetition_event';
    protected $fc_stat_table = 'familycompetition_statistics';
    protected $fc_stat_shop_table = 'familycompetition_shop_statistics';
    protected $user_table = 'user';//用户表
    protected $jdStream_table = 'finance_jd_stream'; //金豆流水
    protected $diamondStream_table = 'finance_diamond_stream'; //钻石流水
    protected $giftStream = 'gift_stream';//礼物流水
    protected $colorGiftRateAwardStreamTable = 'color_gift_rate_award_stream';//彩蛋礼物中奖记录表
    protected $luckDrawTable = 'luck_draw';//许愿池表
    protected $luckDrawAwardLogTable = 'luck_draw_award_log';//许愿池中奖记录表
    protected $wishWheelDrawTable = 'wish_wheel_draw';//许愿转盘-抽奖记录

    protected $lc_event_v3_table = 'grasslandsheep_event_v3';

    protected $excelOutTable = 'all_execel_out';//数据导出表
    protected $frOrderTable = 'finance_recharge_order'; //充值表
    protected $diamondStreamTable = 'finance_diamond_stream'; //流水表
    protected $userLoginLogTable = 'user_login_log'; //用户登录记录

    protected $familyExcelKey = 'FAMILY:OUT:EXCEL:';//全家总动员数据防重复key
    protected $oss;//实例化服务

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        $this->oss = new Oss();
        parent::__construct();
        
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        //查询准备要生成的数据
        $needDatasCount = DB::table($this->excelOutTable)->where('status',0)->count();
        do{
            if($needDatasCount == 0){
                write_log($this->successLog,'无数据',[]);
                break;
            }
            //分页获取数据
            //计算页数
            $nums = 1000;//一次多少条
            $page = ceil($needDatasCount / $nums);
            for ($i = 0; $i < $page; $i++) {
                //偏移量
                $limitset = (($i + 1) - 1) * $nums;
                //获取分页数据
                $datas = DB::table($this->excelOutTable)
                ->where('status',0)
                ->offset($limitset)
                ->limit($nums)
                ->get();
                $datas = objToArray($datas);
                foreach($datas as $val){
                    $condition = json_decode($val['condition'],true);
                    //若没有选择用户id则生成全部数据
                    if(empty($condition['userId'])){
                        if($val['type'] == 1){
                            $startDay = date('Y-m-d H:i:s',$condition['startTime']);
                            $endDay = date('Y-m-d H:i:s',$condition['endTime']);
                            $startTime = Carbon::parse($startDay)->startOfDay()->toDateTimeString();
                            $endTime = Carbon::parse($endDay)->endOfDay()->toDateTimeString();
                            $this->createFamilyExcelByNoUser($val['id'], $startTime,$endTime);
                        }
                    }else{
                        if($val['type'] == 1){
                            $startDay = date('Y-m-d H:i:s',$condition['startTime']);
                            $endDay = date('Y-m-d H:i:s',$condition['endTime']);
                            $startTime = Carbon::parse($startDay)->startOfDay()->toDateTimeString();
                            $endTime = Carbon::parse($endDay)->endOfDay()->toDateTimeString();
                            $this->createFamilyExcelByNoUser($val['id'], $startTime,$endTime,$condition['userId']);
                        }elseif($val['type'] == 2){
                            //kov数据
                            $this->createKovExcel($val['id'],$condition['userId']);
                        }elseif($val['type'] == 3){
                            //礼物销量
                            $this->createGiftStream($val['id'],$condition);
                        }elseif($val['type'] == 4){
                            //礼物销量
                            $this->createGiftStreamLog($val['id'],$condition);
                        }
                    }
                    //玩法汇总数据
                    if($val['type'] == 5){
                        $this->createGameplaySumStat($val['id'],$condition);
                    }
                }
            }
        }while(false);
        return true;
    }

    //生成kov数据
    public function createKovExcel($dataId, $userStr){
        $userids = explode(',',$userStr);
        //获取用户信息
        $userInfo = DB::connection($this->apiCon)->table($this->user_table)
        ->whereIn('uuid', $userids)
        ->select('uuid', 'user_id', 'create_timestamp')
        ->get();
        $userInfo = objToArray($userInfo);
        $lists = [];
        if(!empty($userInfo)){
            $userIds = array_column($userInfo,'user_id');

            //总充值金额钻
            $rechargeAll = DB::connection($this->apiCon)->table($this->frOrderTable)
                ->whereIn('user_id', $userIds)
                ->where('status', 1)
                ->select(DB::raw('sum(diamond_amount) as num,user_id'))
                ->groupBy('user_id')
                ->get();
            $rechargeAll = objToArray($rechargeAll);
            $rechargeAll = array_column($rechargeAll,null,'user_id');

            //总消费钻石
            $diamondAll = DB::connection($this->apiCon)->table($this->diamondStreamTable)
                ->whereIn('user_id', $userIds)
                ->where('diamond_value', '<', 0)
                ->select(DB::raw('sum(diamond_value) as num,user_id'))
                ->groupBy('user_id')
                ->get();
            $diamondAll = objToArray($diamondAll);
            $diamondAll = array_column($diamondAll,null,'user_id');

            foreach($userInfo as $k=>$v){
                //注册时间
                $register = $v['create_timestamp'];
                //注册当日
                $registerDaySt = strtotime(date('Y-m-d 00:00:00', $v['create_timestamp']));
                $registerDayEd = strtotime(date('Y-m-d 23:59:59', $v['create_timestamp']));
                //注册三日后
                $registerDayEd3 = $registerDayEd + 86400 * 3;
                //注册七日后
                $registerDayEd7 = $registerDayEd + 86400 * 7;
                //注册当日充值钻石
                $registerPayData = DB::connection($this->apiCon)->table($this->frOrderTable)
                                ->where('user_id', $v['user_id'])
                                ->where('status', 1)
                                ->where('pay_timestamp', '>=', $registerDaySt)
                                ->where('pay_timestamp', '<=', $registerDayEd)
                                ->select(DB::raw('sum(diamond_amount) as num'))
                                ->first();
                $recharge1 = objToArray($registerPayData);

                //注册当日消费钻石
                $registerDiamond = DB::connection($this->apiCon)->table($this->diamondStreamTable)
                                ->where('user_id', $v['user_id'])
                                ->where('diamond_value', '<', 0)
                                ->where('change_timestamp', '>=', $registerDaySt)
                                ->where('change_timestamp', '<=', $registerDayEd)
                                ->select(DB::raw('sum(diamond_value) as num'))
                                ->first();
                $diamond1 = objToArray($registerDiamond);

                //注册后3日登陆次数
                $loginData = DB::connection($this->apiCon)->table($this->userLoginLogTable)
                ->where('user_id', $v['user_id'])
                ->where('time', '>=', $registerDaySt)
                ->where('time', '<=', $registerDayEd3)
                ->select(DB::raw('count(1) as num'))
                ->first();
                $login3 = objToArray($loginData);

                //3日充值钻石
                $recharge3 = DB::connection($this->apiCon)->table($this->frOrderTable)
                ->where('user_id', $v['user_id'])
                ->where('status', 1)
                ->where('pay_timestamp', '>=', $registerDaySt)
                ->where('pay_timestamp', '<=', $registerDayEd3)
                ->select(DB::raw('sum(diamond_amount) as num'))
                ->first();
                $recharge3 = objToArray($recharge3);

                //3日消费钻石
                $diamond3 = DB::connection($this->apiCon)->table($this->diamondStreamTable)
                    ->where('user_id', $v['user_id'])
                    ->where('diamond_value', '<', 0)
                    ->where('change_timestamp', '>=', $registerDaySt)
                    ->where('change_timestamp', '<=', $registerDayEd3)
                    ->select(DB::raw('sum(diamond_value) as num'))
                    ->first();
                $diamond3 = objToArray($diamond3);

                //7日登陆次数
                $login7 = DB::connection($this->apiCon)->table($this->userLoginLogTable)
                    ->where('user_id', $v['user_id'])
                    ->where('time', '>=', $registerDaySt)
                    ->where('time', '<=', $registerDayEd7)
                    ->select(DB::raw('count(1) as num'))
                    ->first();
                $login7 = objToArray($login7);

                //7日充值钻石
                $recharge7 = DB::connection($this->apiCon)->table($this->frOrderTable)
                    ->where('user_id', $v['user_id'])
                    ->where('status', 1)
                    ->where('pay_timestamp', '>=', $registerDaySt)
                    ->where('pay_timestamp', '<=', $registerDayEd7)
                    ->select(DB::raw('sum(diamond_amount) as num'))
                    ->first();
                $recharge7 = objToArray($recharge7);

                //7日消费钻石
                $diamond7 = DB::connection($this->apiCon)->table($this->diamondStreamTable)
                    ->where('user_id', $v['user_id'])
                    ->where('diamond_value', '<', 0)
                    ->where('change_timestamp', '>=', $registerDaySt)
                    ->where('change_timestamp', '<=', $registerDayEd7)
                    ->select(DB::raw('sum(diamond_value) as num'))
                    ->first();
                $diamond7 = objToArray($diamond7);
                $lists[$k]= [
                    $k+1,
                    $v['uuid'],
                    date("Y-m-d H:i:s", $register),
                    !isset($rechargeAll[$v['user_id']]) ? 0 : $rechargeAll[$v['user_id']]['num'],
                    !isset($diamondAll[$v['user_id']]) ? 0 : abs($diamondAll[$v['user_id']]['num']),
                    empty($recharge1['num']) ? 0 : $recharge1['num'],
                    empty($diamond1['num']) ? 0 : abs($diamond1['num']),
                    empty($login3['num']) ? 0 : $login3['num'],
                    empty($recharge3['num']) ? 0 : $recharge3['num'],
                    empty($diamond3['num']) ? 0 : abs($diamond3['num']),
                    empty($login7['num']) ? 0 : $login7['num'],
                    empty($recharge7['num']) ? 0 : $recharge7['num'],
                    empty($diamond7['num']) ? 0 : abs($diamond7['num']),
                ];
            }
            //生成Excel
            $fileres = $this->CreateExcel($lists,'kov.xlsx',2);
            if($fileres){
                //上传oss
                $url = $this->upLoadOss($fileres['fileName']);
                if($url){
                    //删除文件
                    if (file_exists(storage_path('kov.xlsx'))) {
                        unlink(storage_path('kov.xlsx'));     //删除
                    }
                    //更新表
                    $update = [
                        'status' => 1,
                        'exec_url' => $url,
                        'updated_at' => Carbon::now()->toDateTimeString()
                    ];
                    DB::beginTransaction();
                    try{
                        $status = DB::table($this->excelOutTable)->where('id',$dataId)->update($update);
                        DB::commit();
                        return $status;
                        write_log($this->successLog,'INFO 数据生成成功',$dataId);
                    }catch(\Exception $e){
                        DB::rollBack();
                        write_log($this->errorLog,'ERROR 数据错误',['msg'=>$e->getMessage(),'dataId'=>$dataId]);
                    }
                }
            }else{
                write_log($this->errorLog,'ERROR 数据Excel失败',$lists);  
            }
        }else{
            DB::beginTransaction();
            try{
                $update = [
                    'status' => 2,
                    'exec_url' => '',
                    'updated_at' => Carbon::now()->toDateTimeString()
                ];
                $status = DB::table($this->excelOutTable)->where('id',$dataId)->update($update);
                DB::commit();
                return $status;
                write_log($this->successLog,'IERROR 无数据',$dataId);
            }catch(\Exception $e){
                DB::rollBack();
                write_log($this->errorLog,'ERROR 数据错误',['msg'=>$e->getMessage(),'dataId'=>$dataId]);
            }
            write_log($this->successLog,'ERROR 无数据',[]);  
        }
        
    }

    //生成全家总动员全部数据
    public function createFamilyExcelByNoUser($dataId, $time_st, $time_ed, $userId=0){
        $where = function ($query) use ($userId, $time_st, $time_ed) {
            if(!empty($userId)){
                $query->whereIn('user_id',[$userId]);
            }
            $query->where('created_at','>=',$time_st);
            $query->where('created_at','<=',$time_ed);
        };
        //生成全家总动员数据Excel
        $userIds = DB::connection($this->activityConn)->table($this->fc_event_table)
                ->where($where)
                ->distinct()
                ->pluck('user_id');
        $userIds = objToArray($userIds);

        //用户信息
        $userList = DB::connection($this->apiCon)->table($this->user_table)->select('user_id','uuid','nickname')->whereIn('user_id', $userIds)->get();
        $userList = objToArray($userList);

        //全家总动员-埋点数据
        $info_1 = DB::connection($this->activityConn)->table($this->fc_event_table)
            ->selectRaw('user_id, count(DISTINCT gameNum) as joinNum, count(1) as stakeNumAll, sum(basePrice) as stakePriceAll')
            ->where('created_at', '>=', $time_st)
            ->where('created_at', '<=', $time_ed)
            ->whereIn('user_id', $userIds)
            ->groupBy('user_id')
            ->get();
        $info_1 = objToArray($info_1);
        $info_1 = array_column($info_1, null, 'user_id');
        //全家总动员-埋点数据
        $info_2 = DB::connection($this->activityConn)->table($this->fc_event_table)
            ->selectRaw('user_id, count(1) as stakeNum, sum(rewardPrice) as rewardPriceAll')
            ->where('created_at', '>=', $time_st)
            ->where('created_at', '<=', $time_ed)
            ->where('status', 1)
            ->whereIn('user_id', $userIds)
            ->groupBy('user_id')
            ->get();
        $info_2 = objToArray($info_2);
        $info_2 = array_column($info_2, null, 'user_id');

        $info_3 = DB::connection($this->apiCon)->table($this->diamondStream_table)
            ->selectRaw('user_id, sum(diamond_value) as buyGoodDiamondNum')
            ->where('change_time', '>=', $time_st)
            ->where('change_time', '<=', $time_ed)
            ->where('diamond_type', 100)
            ->whereIn('user_id', $userIds)
            ->groupBy('user_id')
            ->get();
        $info_3 = objToArray($info_3);
        $info_3 = array_column($info_3, null, 'user_id');

        $info_4 = DB::connection($this->apiCon)->table($this->jdStream_table)
            ->selectRaw('user_id, sum(change_value) as buyGiftJdNum')
            ->where('created_at', '>=', $time_st)
            ->where('created_at', '<=', $time_ed)
            ->where('jd_type', 51)
            ->whereIn('user_id', $userIds)
            ->groupBy('user_id')
            ->get();
        $info_4 = objToArray($info_4);
        $info_4 = array_column($info_4, null, 'user_id');
        $csvData = [];
        foreach($userList as $k=>$val) {
            //参与次数
            $joinNum = empty($info_1[$val['user_id']]['joinNum']) ? 0 : $info_1[$val['user_id']]['joinNum'];
            //押注次数
            $stakeNumAll = empty($info_1[$val['user_id']]['stakeNumAll']) ? 0 : $info_1[$val['user_id']]['stakeNumAll'];
            //押中次数
            $stakeNum = empty($info_2[$val['user_id']]['stakeNum']) ? 0 : $info_2[$val['user_id']]['stakeNum'];
            //消耗金豆数量
            $stakePriceAll = empty($info_1[$val['user_id']]['stakePriceAll']) ? 0 : $info_1[$val['user_id']]['stakePriceAll'];
            //产出金豆数量
            $rewardPriceAll = empty($info_2[$val['user_id']]['rewardPriceAll']) ? 0 : $info_2[$val['user_id']]['rewardPriceAll'];
            //利润金豆数量 产出-消耗
            $porfitNum =  $rewardPriceAll - $stakePriceAll;
            //利润率 利润金豆/消耗金豆
            $porfitRate = empty($stakePriceAll) ? 0 : round($porfitNum/$stakePriceAll, 6);
            //购买道具钻石数
            $buyGoodDiamondNum = abs(empty($info_3[$val['user_id']]['buyGoodDiamondNum']) ? 0 : $info_3[$val['user_id']]['buyGoodDiamondNum']);
            $csvData[$k] = [
                $val['nickname'],
                $val['user_id'],
                $val['uuid'],
                $joinNum,
                $stakeNumAll,
                $stakeNum,
                $stakePriceAll,
                $rewardPriceAll,
                $porfitNum,
                $porfitRate,
                $buyGoodDiamondNum
            ]; 
        }
        //生成Excel
        $fileres = $this->CreateExcel($csvData,'family.xlsx',1);
        if($fileres){
            //上传oss
            $url = $this->upLoadOss($fileres['fileName']);
            if($url){
                //删除文件
                if (file_exists(storage_path('family.xlsx'))) {
                    unlink(storage_path('family.xlsx'));     //删除
                }
                //更新表
                $update = [
                    'status' => 1,
                    'exec_url' => $url,
                    'updated_at' => Carbon::now()->toDateTimeString()
                ];
                DB::beginTransaction();
                try{
                    DB::table($this->excelOutTable)->where('id',$dataId)->update($update);
                    DB::commit();
                    return false;
                    write_log($this->successLog,'INFO 数据生成成功',$dataId);
                }catch(\Exception $e){
                    DB::rollBack();
                    write_log($this->errorLog,'ERROR 数据错误',['msg'=>$e->getMessage(),'dataId'=>$dataId]);
                }
            }
        }else{
            write_log($this->errorLog,'ERROR 数据Excel失败',$csvData);  
        }
    }   

    //生成礼物礼物销量
    public function createGiftStream($dataId, $condition){
        $where = function ($query) use ($condition) {
            if(!empty($condition['startTime'])){
                $query->where('change_timestamp','>=',$condition['startTime']); 
            }
            if(!empty($condition['endTime'])){
                $query->where('change_timestamp','<=',$condition['endTime']); 
            }
            if(!empty($condition['gift_ids'])){
                $goodsIds = explode(',',$condition['gift_ids']);
                $query->whereIn('gift_id',$goodsIds);
            }
            if($condition['is_bag'] != 0){
                $query->where('type',$condition['is_bag']);
            }
        };
        $res = DB::connection($this->apiCon)
                ->table($this->giftStream)
                ->selectRaw(DB::raw("date_format(FROM_UNIXTIME(change_timestamp),'%Y-%m-%d')as date,gift_id,SUM(gift_num) as num,gift_price,SUM(gift_price*gift_num) as total,count(DISTINCT from_user_id) as send_people,type"))
                ->where($where)
                ->groupBy('date','gift_id')
                ->get();
        $res = objToArray($res);
        //生成Excel
        $fileres = $this->CreateExcel($res,'giftDetial.xlsx',3);
        if($fileres){
            //上传oss
            $url = $this->upLoadOss($fileres['fileName']);
            if($url){
                //删除文件
                if (file_exists(storage_path('giftDetial.xlsx'))) {
                    unlink(storage_path('giftDetial.xlsx'));     //删除
                }
                //更新表
                $update = [
                    'status' => 1,
                    'exec_url' => $url,
                    'updated_at' => Carbon::now()->toDateTimeString()
                ];
                DB::beginTransaction();
                try{
                    DB::table($this->excelOutTable)->where('id',$dataId)->update($update);
                    DB::commit();
                    return false;
                    write_log($this->successLog,'INFO 数据生成成功',$dataId);
                }catch(\Exception $e){
                    DB::rollBack();
                    write_log($this->errorLog,'ERROR 数据错误',['msg'=>$e->getMessage(),'dataId'=>$dataId]);
                }
            }
        }else{
            write_log($this->errorLog,'ERROR 数据Excel失败',$res);  
        }
    }

    //生成送礼详情
    public function createGiftStreamLog($dataId, $condition){
        $where = function($query)use($condition) {
            if(!empty($condition['startTime'])){
                $query->where('change_timestamp','>=',$condition['startTime']); 
            }
            if(!empty($condition['endTime'])){
                $query->where('change_timestamp','<=',$condition['endTime']); 
            }
            if(!empty($condition['gift_ids'])){
                $goodsIds = explode(',',$condition['gift_ids']);
                $query->whereIn('gift_id',$goodsIds);
            }
            if(!empty($condition['from_user_ids'])){
                //uuid查询用户userId
                $sendUserIds = explode(',',$condition['from_user_ids']);
                $userIds = DB::connection($this->apiCon)->table($this->user_table)->whereIn('uuid',$sendUserIds)->get(['user_id']);
                $userIds = objToArray($userIds);
                $userIds = array_column($userIds,'user_id');
                $query->whereIn('from_user_id',$userIds);
            }
            if(!empty($condition['to_user_ids'])){
                //uuid查询用户userId
                $toUserIds = explode(',',$condition['to_user_ids']);
                $userIds = DB::connection($this->apiCon)->table($this->user_table)->whereIn('uuid',$toUserIds)->get(['user_id']);
                $userIds = objToArray($userIds);
                $userIds = array_column($userIds,'user_id');
                $query->whereIn('user_id',$userIds);
            }
        };
        $res = DB::connection($this->apiCon)
                ->table($this->giftStream)
                ->select(DB::raw('FROM_UNIXTIME(change_timestamp) as date,user_id,from_user_id,gift_id,gift_name,gift_type,type,gift_num as gift_num,gift_price,gift_num*gift_price as total'))
                ->where($where)
                ->orderBy('change_timestamp','desc')
                ->get();
        $res = objToArray($res);
        //生成Excel
        $fileres = $this->CreateExcel($res,'giftsend.xlsx',4);
        if($fileres){
            //上传oss
            $url = $this->upLoadOss($fileres['fileName']);
            if($url){
                //删除文件
                if (file_exists(storage_path('giftsend.xlsx'))) {
                    unlink(storage_path('giftsend.xlsx'));     //删除
                }
                //更新表
                $update = [
                    'status' => 1,
                    'exec_url' => $url,
                    'updated_at' => Carbon::now()->toDateTimeString()
                ];
                DB::beginTransaction();
                try{
                    DB::table($this->excelOutTable)->where('id',$dataId)->update($update);
                    DB::commit();
                    return false;
                    write_log($this->successLog,'INFO 数据生成成功',$dataId);
                }catch(\Exception $e){
                    DB::rollBack();
                    write_log($this->errorLog,'ERROR 数据错误',['msg'=>$e->getMessage(),'dataId'=>$dataId]);
                }
            }
        }else{
            write_log($this->errorLog,'ERROR 数据Excel失败',$res);  
        }
    }

    //玩法汇总数据
    public function createGameplaySumStat($dataId, $condition){
        $userId = empty($condition['userId']) ? 0 : $condition['userId'];
        $date_st = empty($condition['startTime']) ? '' : $condition['startTime'];
        $date_ed = empty($condition['endTime']) ? '' : $condition['endTime'];

        //彩蛋
        $query1 = DB::connection($this->apiCon)->table($this->colorGiftRateAwardStreamTable)
            ->selectRaw('user_id, sum(color_gift_price) as color_gift_price, sum(award_gift_price) as award_gift_price');
        if(!empty($userId)) {
            $query1 = $query1->where('user_id', $userId);
        }
        if(!empty($date_st)) {
            $query1 = $query1->where('created_at', '>=', date("Y-m-d H:i:s", strtotime($date_st)));
        }
        if(!empty($date_ed)) {
            $query1 = $query1->where('created_at', '<=', date("Y-m-d H:i:s", strtotime($date_ed)));
        }
        $data1 = $query1->groupBy('user_id')->get();
        $data1 = array_column(objToArray($data1), null, 'user_id');

        //森林狩猎
        $grasslandsheepPrice = 10;
        //消耗
        $query2 = DB::connection($this->activityConn)->table($this->lc_event_v3_table)
            ->selectRaw('user_id, sum(basePrice) as basePrice')->where('is_delete', 0);
        if(!empty($userId)) {
            $query2 = $query2->where('user_id', $userId);
        }
        if(!empty($date_st)) {
            $query2 = $query2->where('created_at', '>=', date("Y-m-d H:i:s", strtotime($date_st)));
        }
        if(!empty($date_ed)) {
            $query2 = $query2->where('created_at', '<=', date("Y-m-d H:i:s", strtotime($date_ed)));
        }
        $data2 = $query2->groupBy('user_id')->get();
        $data2 = array_column(objToArray($data2), null, 'user_id');
        //产出
        $query3 = DB::connection($this->activityConn)->table($this->lc_event_v3_table)
            ->selectRaw('user_id, sum(rewardPrice) as rewardPrice')->where('is_delete', 0);
        if(!empty($userId)) {
            $query3 = $query3->where('user_id', $userId);
        }
        if(!empty($date_st)) {
            $query3 = $query3->where('created_at', '>=', date("Y-m-d H:i:s", strtotime($date_st)));
        }
        if(!empty($date_ed)) {
            $query3 = $query3->where('created_at', '<=', date("Y-m-d H:i:s", strtotime($date_ed)));
        }
        $data3 = $query3->groupBy('user_id')->get();
        $data3 = array_column(objToArray($data3), null, 'user_id');

        //许愿池
        $query4 = DB::connection($this->apiCon)->table($this->luckDrawAwardLogTable.' as a')
            ->leftJoin($this->luckDrawTable.' as b' ,'a.luck_id','=','b.id')
            ->selectRaw('zb_a.user_id, sum(zb_a.num*zb_a.price) as reward_price, sum(zb_a.num*zb_b.price) as draw_price');
        if(!empty($userId)) {
            $query4 = $query4->where('a.user_id', $userId);
        }
        if(!empty($date_st)) {
            $query4 = $query4->where('a.created_at', '>=', date("Y-m-d H:i:s", strtotime($date_st)));
        }
        if(!empty($date_ed)) {
            $query4 = $query4->where('a.created_at', '<=', date("Y-m-d H:i:s", strtotime($date_ed)));
        }
        $data4 = $query4->groupBy('a.user_id')->get();
        $data4 = array_column(objToArray($data4), null, 'user_id');

        //许愿转盘
        $query5 = DB::connection($this->apiCon)->table($this->wishWheelDrawTable)
            ->selectRaw('user_id, sum(prop_diamond_num) as prop_diamond_num, sum(reward_diamond_num) as reward_diamond_num');
        if(!empty($userId)) {
            $query5 = $query5->where('user_id', $userId);
        }
        if(!empty($date_st)) {
            $query5 = $query5->where('created_at', '>=', date("Y-m-d H:i:s", strtotime($date_st)));
        }
        if(!empty($date_ed)) {
            $query5 = $query5->where('created_at', '<=', date("Y-m-d H:i:s", strtotime($date_ed)));
        }
        $data5 = $query5->groupBy('user_id')->get();
        $data5 = array_column(objToArray($data5), null, 'user_id');

        //用户信息
        $userIdsAll = array_unique(array_merge(array_column($data1, 'user_id'), array_column($data2, 'user_id'), array_column($data3, 'user_id'), array_column($data4, 'user_id'), array_column($data5, 'user_id')));
        rsort($userIdsAll);

        $userList = DB::connection($this->apiCon)->table($this->user_table)->whereIn('user_id', $userIdsAll)->select('user_id','nickname','uuid')->get();
        $userList = array_column(objToArray($userList), null, 'user_id');

        //整理
        $res = [];
        foreach($userList as $val) {
            $item = [];
            $item['user_id'] = $val['user_id'];
            $item['uuid'] = $val['uuid'];
            $item['nickname'] = $val['nickname'];
            //彩蛋
            $item['color_pay_num'] = empty($data1[$item['user_id']]['color_gift_price']) ? 0 : $data1[$item['user_id']]['color_gift_price'];
            $item['color_get_num'] = empty($data1[$item['user_id']]['award_gift_price']) ? 0 : $data1[$item['user_id']]['award_gift_price'];
            //森林狩猎
            $item['gl_pay_num'] = empty($data2[$item['user_id']]['basePrice']) ? 0 : $data2[$item['user_id']]['basePrice']*$grasslandsheepPrice;
            $item['gl_get_num'] = empty($data3[$item['user_id']]['rewardPrice']) ? 0 : $data3[$item['user_id']]['rewardPrice']*$grasslandsheepPrice;
            //许愿池
            $item['luck_pay_num'] = empty($data4[$item['user_id']]['draw_price']) ? 0 : $data4[$item['user_id']]['draw_price'];
            $item['luck_get_num'] = empty($data4[$item['user_id']]['reward_price']) ? 0 : $data4[$item['user_id']]['reward_price'];
            //许愿转盘
            $item['wish_pay_num'] = empty($data5[$item['user_id']]['prop_diamond_num']) ? 0 : $data5[$item['user_id']]['prop_diamond_num'];
            $item['wish_get_num'] = empty($data5[$item['user_id']]['reward_diamond_num']) ? 0 : $data5[$item['user_id']]['reward_diamond_num'];
            //合计
            $item['pay_num'] = $item['color_pay_num'] + $item['gl_pay_num'] + $item['luck_pay_num'] + $item['wish_pay_num'];
            $item['get_num'] = $item['color_get_num'] + $item['gl_get_num'] + $item['luck_get_num'] + $item['wish_get_num'];
            //投入产出比：产出/消耗
            $item['profit_rate'] = (empty($item['pay_num']) ? 0 : round($item['get_num']/$item['pay_num'], 6)*100).'%';
            //盈亏：投入-消耗
            $item['profit_num'] = $item['pay_num'] - $item['get_num'];

            $res[] = $item;
        }

        //生成Excel
        $fileres = $this->CreateExcel($res, 'gameplaySumStat-'.$dataId.'.xlsx', 5);
        if($fileres){
            //上传oss
            $url = $this->upLoadOss($fileres['fileName']);
            if($url){
                //删除文件
                if (file_exists(storage_path('giftsend.xlsx'))) {
                    unlink(storage_path('giftsend.xlsx'));     //删除
                }
                //更新表
                $update = [
                    'status' => 1,
                    'exec_url' => $url,
                    'updated_at' => Carbon::now()->toDateTimeString()
                ];
                DB::beginTransaction();
                try{
                    DB::table($this->excelOutTable)->where('id',$dataId)->update($update);
                    DB::commit();
                    return false;
                    write_log($this->successLog,'INFO 数据生成成功',$dataId);
                }catch(\Exception $e){
                    DB::rollBack();
                    write_log($this->errorLog,'ERROR 数据错误',['msg'=>$e->getMessage(),'dataId'=>$dataId]);
                }
            }
        }else{
            write_log($this->errorLog,'ERROR 数据Excel失败',$res);  
        }
    }

    //上传OSS
    public function upLoadOss($fileName=''){
        if(empty($fileName)){
            write_log($this->successLog,'文件不存在',[]);
            return false;
        }
        $upload_config =  curl_request(config('kkaudio.kkaudio_api') . "/inner/system/put_file_url", ['type' => 24, 'exts' => 'xlsx', 'noase' => 1], $contentType = 'urlencoded', $httpMethod = 'GET');

        $uri = $upload_config['data'][0]['uri'];
        $formatUri = ltrim($uri, '/');
        $localFilePath = storage_path($fileName);
        $ossRes = $this->oss->uploadFile($formatUri, $localFilePath);
        if (!$ossRes) {
           write_log($this->errorLog, '上传文件过程中发生错误',$ossRes);
           return false;
        }
        $url = '/'.$formatUri;
        return $url;
    }

    //excel 生成
    public function CreateExcel($csvData, $fileName='data.excel', $type=1){
        //生成EXCEL
        $objPHPExcel = new PHPExcel();
        try {
            $objSheet = $objPHPExcel->getActiveSheet();
            if($type == 1){
                $this->excelFamilyTemp($objSheet, $objPHPExcel, $csvData);
            }elseif($type == 2){
                $this->excelKovTemp($objSheet, $objPHPExcel, $csvData);
            }elseif($type == 3){
                $this->excelGiftDetialTemp($objSheet, $objPHPExcel, $csvData);
            }elseif($type == 4){
                $this->excelGiftSendDetialTemp($objSheet, $objPHPExcel, $csvData);
            }elseif($type == 5){
                $this->excelGameplaySumStat($objSheet, $objPHPExcel, $csvData);
            }
                
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
            $filePath = storage_path($fileName);
            $objWriter->save($filePath);
            
            return ['fileName' => $fileName, 'filePath' => $filePath];
        } catch (\PHPExcel_Exception $e) {  
            write_log($this->successLog,'生成Excel数据失败',[$e]);
            return false;
        }
    }

    //kov的Excel模版
    public function excelKovTemp($objSheet, $objPHPExcel, $csvData){
        //工作表标题
        $objSheet->setTitle("kov数据");
        //第一行内容，放列标题
        $objSheet->setCellValue("A1", "序列");
        $objSheet->setCellValue("B1", "uuid");
        $objSheet->setCellValue("C1", "注册时间");
        $objSheet->setCellValue("D1", "总充值钻石");
        $objSheet->setCellValue("E1", "总消费钻石");
        $objSheet->setCellValue("F1", "当日充值钻石");
        $objSheet->setCellValue("G1", "当日消费钻石");
        $objSheet->setCellValue("H1", "3日登录次数");
        $objSheet->setCellValue("I1", "3日充值钻石");
        $objSheet->setCellValue("J1", "3日消费钻石");
        $objSheet->setCellValue("K1", "7日登录次数");
        $objSheet->setCellValue("L1", "7日充值钻石");
        $objSheet->setCellValue("M1", "7日消耗钻石");
        //设置第一行的背景颜色
        $objPHPExcel->getActiveSheet()
            ->getStyle('A1:M1')
            ->getFill()
            ->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()
            ->setRGB('FFFF00');
        //后面行循环放内容
        for($i=0;$i<count($csvData);$i++){
            $lineNum = $i+2;
            $objSheet->setCellValue("A".$lineNum, $csvData[$i][0]);
            $objSheet->setCellValue("B".$lineNum, $csvData[$i][1]);
            $objSheet->setCellValue("C".$lineNum, $csvData[$i][2]);
            $objSheet->setCellValue("D".$lineNum, $csvData[$i][3]);
            $objSheet->setCellValue("E".$lineNum, $csvData[$i][4]);
            $objSheet->setCellValue("F".$lineNum, $csvData[$i][5]);
            $objSheet->setCellValue("G".$lineNum, $csvData[$i][6]);
            $objSheet->setCellValue("H".$lineNum, $csvData[$i][7]);
            $objSheet->setCellValue("I".$lineNum, $csvData[$i][8]);
            $objSheet->setCellValue("J".$lineNum, $csvData[$i][9]);
            $objSheet->setCellValue("K".$lineNum, $csvData[$i][10]);
            $objSheet->setCellValue("L".$lineNum, $csvData[$i][11]);
            $objSheet->setCellValue("M".$lineNum, $csvData[$i][12]);
        }
    }

    //全家总动员Excel模版
    public function excelFamilyTemp($objSheet, $objPHPExcel, $csvData){
        //工作表标题
        $objSheet->setTitle("用户参与明细");
        //第一行内容，放列标题
        $objSheet->setCellValue("A1", "用户名称");
        $objSheet->setCellValue("B1", "用户id");
        $objSheet->setCellValue("C1", "用户UUID");
        $objSheet->setCellValue("D1", "参与次数");
        $objSheet->setCellValue("E1", "押注次数");
        $objSheet->setCellValue("F1", "押中次数");
        $objSheet->setCellValue("G1", "消耗金豆数");
        $objSheet->setCellValue("H1", "产出金豆数");
        $objSheet->setCellValue("I1", "利润金豆数");
        $objSheet->setCellValue("J1", "利润率");
        $objSheet->setCellValue("K1", "购买钻石道具数");
        //设置第一行的背景颜色
        $objPHPExcel->getActiveSheet()
            ->getStyle('A1:K1')
            ->getFill()
            ->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()
            ->setRGB('FFFF00');
        //后面行循环放内容
        for($i=0;$i<count($csvData);$i++){
            $lineNum = $i+2;
            $objSheet->setCellValue("A".$lineNum, $csvData[$i][0]);
            $objSheet->setCellValue("B".$lineNum, $csvData[$i][1]);
            $objSheet->setCellValue("C".$lineNum, $csvData[$i][2]);
            $objSheet->setCellValue("D".$lineNum, $csvData[$i][3]);
            $objSheet->setCellValue("E".$lineNum, $csvData[$i][4]);
            $objSheet->setCellValue("F".$lineNum, $csvData[$i][5]);
            $objSheet->setCellValue("G".$lineNum, $csvData[$i][6]);
            $objSheet->setCellValue("H".$lineNum, $csvData[$i][7]);
            $objSheet->setCellValue("I".$lineNum, $csvData[$i][8]);
            $objSheet->setCellValue("J".$lineNum, $csvData[$i][9]);
            $objSheet->setCellValue("K".$lineNum, $csvData[$i][10]);
        }
    }

    //礼物详情模板
    public function excelGiftDetialTemp($objSheet, $objPHPExcel, $csvData){
        //工作表标题
        $objSheet->setTitle("礼物流水详情");
        //第一行内容，放列标题
        $objSheet->setCellValue("A1", "日期");
        $objSheet->setCellValue("B1", "礼物ID");
        $objSheet->setCellValue("C1", "礼物销量");
        $objSheet->setCellValue("D1", "礼物单价");
        $objSheet->setCellValue("E1", "礼物消耗（只算礼物栏送出）");
        $objSheet->setCellValue("F1", "礼物送出人数");
        $objSheet->setCellValue("G1", "是否包含背包");
        //设置第一行的背景颜色
        $objPHPExcel->getActiveSheet()
            ->getStyle('A1:G1')
            ->getFill()
            ->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()
            ->setRGB('FFFF00');
        //后面行循环放内容
        for($i=0;$i<count($csvData);$i++){
            $lineNum = $i+2;
            $objSheet->setCellValue("A".$lineNum, $csvData[$i]['date']);
            $objSheet->setCellValue("B".$lineNum, $csvData[$i]['gift_id']);
            $objSheet->setCellValue("C".$lineNum, $csvData[$i]['num']);
            $objSheet->setCellValue("D".$lineNum, $csvData[$i]['gift_price']);
            $objSheet->setCellValue("E".$lineNum, $csvData[$i]['total']);
            $objSheet->setCellValue("F".$lineNum, $csvData[$i]['send_people']);
            if($csvData[$i]['type'] == 1){
                $is_bage = '否';
            }else{
                $is_bage = '是';
            }
            $objSheet->setCellValue("G".$lineNum, $is_bage);
        }
    }

    //送礼详情模板
    public function excelGiftSendDetialTemp($objSheet, $objPHPExcel, $csvData){
        //工作表标题
        $objSheet->setTitle("送礼详情");
        //第一行内容，放列标题
        $objSheet->setCellValue("A1", "日期");
        $objSheet->setCellValue("B1", "收礼ID");
        $objSheet->setCellValue("C1", "送礼ID");
        $objSheet->setCellValue("D1", "礼物ID");
        $objSheet->setCellValue("E1", "礼物名称");
        $objSheet->setCellValue("F1", "礼物类型");
        $objSheet->setCellValue("G1", "送出场景");
        $objSheet->setCellValue("H1", "礼物数量");
        $objSheet->setCellValue("I1", "礼物单价");
        $objSheet->setCellValue("J1", "礼物消耗");
        //设置第一行的背景颜色
        $objPHPExcel->getActiveSheet()
            ->getStyle('A1:J1')
            ->getFill()
            ->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()
            ->setRGB('FFFF00');
        //后面行循环放内容
        for($i=0;$i<count($csvData);$i++){
            $lineNum = $i+2;
            $objSheet->setCellValue("A".$lineNum, $csvData[$i]['date']);
            $objSheet->setCellValue("B".$lineNum, $csvData[$i]['user_id']);
            $objSheet->setCellValue("C".$lineNum, $csvData[$i]['from_user_id']);
            $objSheet->setCellValue("D".$lineNum, $csvData[$i]['gift_id']);
            $objSheet->setCellValue("E".$lineNum, $csvData[$i]['gift_name']);
            if($csvData[$i]['gift_type'] == 1){
                $gift_type = '普通礼物';
            }else{
                $gift_type = '幸运礼物';
            }
            $objSheet->setCellValue("F".$lineNum, $gift_type);
            if($csvData[$i]['type'] == 1){
                $is_bage = '礼物栏';
            }else{
                $is_bage = '背包';
            }
            $objSheet->setCellValue("G".$lineNum, $is_bage);
            $objSheet->setCellValue("H".$lineNum, $csvData[$i]['gift_num']);
            $objSheet->setCellValue("I".$lineNum, $csvData[$i]['gift_price']);
            $objSheet->setCellValue("J".$lineNum, $csvData[$i]['total']);
        }
    }

    //玩法汇总数据模板
    public function excelGameplaySumStat($objSheet, $objPHPExcel, $csvData){
        //工作表标题
        $objSheet->setTitle("送礼详情");
        //第一行内容，放列标题
        $objSheet->setCellValue("A1", "用户ID");
        $objSheet->setCellValue("B1", "用户UUID");
        $objSheet->setCellValue("C1", "用户昵称");
        $objSheet->setCellValue("D1", "投入钻石");
        $objSheet->setCellValue("E1", "产出钻石");
        $objSheet->setCellValue("F1", "投入产出比");
        $objSheet->setCellValue("G1", "盈亏");
        //设置第一行的背景颜色
        $objPHPExcel->getActiveSheet()
            ->getStyle('A1:J1')
            ->getFill()
            ->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()
            ->setRGB('FFFF00');
        //后面行循环放内容
        for($i=0;$i<count($csvData);$i++){
            $lineNum = $i+2;
            $objSheet->setCellValue("A".$lineNum, $csvData[$i]['user_id']);
            $objSheet->setCellValue("B".$lineNum, $csvData[$i]['uuid']);
            $objSheet->setCellValue("C".$lineNum, $csvData[$i]['nickname']);
            $objSheet->setCellValue("D".$lineNum, $csvData[$i]['pay_num']);
            $objSheet->setCellValue("E".$lineNum, $csvData[$i]['get_num']);
            $objSheet->setCellValue("F".$lineNum, $csvData[$i]['profit_rate']);
            $objSheet->setCellValue("G".$lineNum, $csvData[$i]['profit_num']);
        }
    }

}
